package com.example.bootdemo.common.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class ReadExcelUtil {
    public int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    /**
     * read the Excel .xlsx,.xls
     * @param file jsp中的上传文件
     * @return
     * @throws IOException
     */
    public Map<String,Object> readExcel(MultipartFile file) throws IOException {
        if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
            return null;
        }else{
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            if(!ExcelUtil.EMPTY.equals(postfix)){
                if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
//                    return readXls(file);
                }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
                    return readXlsx(file);
                }else{
                    return null;
                }
            }
        }
        return null;
    }
    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     * @throws IOException
     */
    @SuppressWarnings("deprecation")
    public Map<String,Object> readXlsx(MultipartFile file){
        Map<String,Object> map=new LinkedHashMap<>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<Map<String,Object>> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet(页)
//            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
//                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                XSSFSheet xssfSheet = wb.getSheetAt(0);
//                if(xssfSheet == null){
//                    continue;
//                }
                List<ArrayList<Map<String,Object>>> list = new ArrayList<ArrayList<Map<String,Object>>>();
                totalRows=getExcelRealRow(xssfSheet);
                //totalRows = xssfSheet.getLastRowNum();
                //读取Row,从第3行开始，应该从第三行开始读
                for(int rowNum = 2;rowNum <=totalRows;rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    Map<String,Object> map1=new HashMap();
                    if(xssfRow!=null){
                        rowList = new ArrayList<>();
                        totalCells = xssfSheet.getRow(0).getPhysicalNumberOfCells();
                        //读取列，从第二列开始
                        for(int c=1;c<totalCells;c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            Map<String,Object> mapval=new HashMap<>();
                            int count=1;
                            if(cell==null){
                                mapval.put("value","");
                                rowList.add(mapval);
                                continue;
                            }
                            mapval.put("value",ExcelUtil.getXValue(cell).trim());
                            if (cell.getCellComment()!=null){
                                mapval.put("remark",cell.getCellComment().getString().getString());
                            }else {
                                mapval.put("remark",null);
                            }

                            map1.put("name",mapval);

                            rowList.add(mapval);
                            /*if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }
                            rowList.add(ExcelUtil.getXValue(cell).trim());*/
                        }
                    }
                    list.add(rowList);
                }
            map.put("sheet1",list);
//                if (numSheet==0){
//                    map.put("sheet1",list);
//                }
//                if (numSheet==1){
//                    map.put("sheet2",list);
//                }
//                if (numSheet==2){
//                    map.put("sheet3",list);
//                }
//                if (numSheet==3){
//                    map.put("sheet4",list);
//                }
//            }
            return map;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
    /**
     * read the Excel 2003-2007 .xls
     * @param file
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readXls(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null){
                    continue;
                }
                totalRows=getExcelRealRow(hssfSheet);
                //totalRows = hssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow!=null){
                        rowList = new ArrayList<String>();
                        totalCells = hssfSheet.getRow(0).getPhysicalNumberOfCells();
                        //读取列，从第一列开始
                        for(short c=0;c<totalCells;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }
                            rowList.add(ExcelUtil.getHValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 获取Excel表的真实行数
     *
     * @param sheet
     * @return
     */
    public static int getExcelRealRow(Sheet sheet) {
        boolean flag = false;
        for (int i = 1; i <= sheet.getLastRowNum(); ) {
            Row r = sheet.getRow(i);
            if (r == null) {
                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                continue;
            }
            flag = false;
            for (Cell c : r) {
                if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    flag = true;
                    break;
                }
            }
            if (flag) {
                i++;
                continue;
            } else {
                // 如果是空白行（即可能没有数据，但是有一定格式）
                if (i == sheet.getLastRowNum())// 如果到了最后一行，直接将那一行remove掉
                    sheet.removeRow(r);
                else//如果还没到最后一行，则数据往上移一行
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
            }
        }
        return sheet.getLastRowNum();
    }

}
